use serde::{Deserialize, Serialize};
use std::fs;
use std::path::PathBuf;
use oracle::{Connector};
use crate::commands::des_util::DesUtil;
use chrono::Local;
use rusqlite::{Connection, Result as SqliteResult};
use std::sync::Mutex;
use std::sync::OnceLock;

const USER_INFO_JSON: &str = include_str!("../../user_info.json");

// 全局数据库连接
static DB_CONNECTION: OnceLock<Result<Mutex<Connection>, String>> = OnceLock::new();

#[derive(Serialize, Deserialize, Clone)]
pub struct AISettings {
    pub api_key: String,
}

impl Default for AISettings {
    fn default() -> Self {
        Self {
            api_key: String::new(),
        }
    }
}


#[derive(Serialize, Deserialize, Clone)]
pub struct DatabaseConfig {
    pub host: String,
    pub port: u16,
    pub service_name: String,
    pub username: String,
    pub password: String,
}

impl Default for DatabaseConfig {
    fn default() -> Self {
        Self {
            host: "localhost".to_string(),
            port: 1521,
            service_name: "ORCL".to_string(),
            username: "AH_SLM4".to_string(),
            password: "2".to_string(),
        }
    }
}

#[derive(Serialize, Deserialize, Clone)]
pub struct EnvironmentConfig {
    pub env_55: String,
    pub env_54: String,
    pub localhost: String,
    pub key: String, // 新增key字段
}

impl Default for EnvironmentConfig {
    fn default() -> Self {
        Self {
            env_55: "http://10.243.165.55:8080".to_string(),
            env_54: "http://10.243.165.54:8085".to_string(),
            localhost: "http://localhost:8080".to_string(),
            key: "".to_string(), // 新增key字段默认值
        }
    }
}

#[derive(Serialize, Deserialize, Clone)]
pub struct AutoLoginConfig {
    pub environments: EnvironmentConfig,
}

impl Default for AutoLoginConfig {
    fn default() -> Self {
        Self {
            environments: EnvironmentConfig::default(),
        }
    }
}

#[derive(Serialize, Deserialize)]
pub struct UserSettings {
    pub nickname: String,
    pub theme: String,
    pub database: DatabaseConfig,
    pub auto_login: AutoLoginConfig,
    pub ai_settings: AISettings
}

impl Default for UserSettings {
    fn default() -> Self {
        Self {
            nickname: "用户".to_string(),
            theme: "light".to_string(),
            database: DatabaseConfig::default(),
            auto_login: AutoLoginConfig::default(),
            ai_settings: AISettings::default(),  
        }
    }
}

#[derive(Serialize, Deserialize, Clone)]
pub struct UserInfo {
    pub userid: String,
    pub username: String,
    pub pinyin: String,
    pub firstpy: String,
    pub orgname: String,
}

#[derive(Serialize, Deserialize, Clone)]
pub struct LoginRecord {
    pub id: Option<i64>,
    pub user_id: String,
    pub username: String,
    pub environment: String,
    pub org_name: String,
    pub click_count: i64,
    pub last_used: String,
    pub created_at: String,
}

#[derive(Serialize, Deserialize, Clone)]
pub struct RankingItem {
    pub user_id: String,
    pub username: String,
    pub environment: String,
    pub org_name: String,
    pub click_count: i64,
    pub last_used: String,
    pub rank: i64,
}

// 获取数据库连接
fn get_db_connection() -> Result<&'static Mutex<Connection>, String> {
    let result = DB_CONNECTION.get_or_init(|| {
        let db_path = match get_login_stats_db_path() {
            Ok(path) => path,
            Err(e) => return Err(e),
        };

        match Connection::open(&db_path) {
            Ok(conn) => Ok(Mutex::new(conn)),
            Err(e) => Err(format!("打开数据库失败: {}", e)),
        }
    });

    match result {
        Ok(mutex) => Ok(mutex),
        Err(e) => Err(e.clone()),
    }
}

// 获取数据库文件路径
fn get_login_stats_db_path() -> Result<PathBuf, String> {
    let mut path = get_app_data_dir()?;
    path.push("login_stats.db");
    Ok(path)
}

// 初始化数据库
#[tauri::command]
pub async fn init_login_stats_db() -> Result<(), String> {
    let conn = get_db_connection()?;
    let conn = conn.lock().map_err(|e| format!("获取数据库锁失败: {}", e))?;
    
    conn.execute(
        "CREATE TABLE IF NOT EXISTS login_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            username TEXT NOT NULL,
            environment TEXT NOT NULL,
            org_name TEXT,
            click_count INTEGER DEFAULT 1,
            last_used TEXT NOT NULL,
            created_at TEXT NOT NULL,
            UNIQUE(user_id, environment)
        )",
        [],
    ).map_err(|e| format!("创建表失败: {}", e))?;
    
    // 创建索引
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_user_env ON login_records(user_id, environment)",
        [],
    ).map_err(|e| format!("创建索引失败: {}", e))?;
    
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_click_count ON login_records(click_count DESC)",
        [],
    ).map_err(|e| format!("创建索引失败: {}", e))?;
    
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_last_used ON login_records(last_used DESC)",
        [],
    ).map_err(|e| format!("创建索引失败: {}", e))?;
    
    Ok(())
}

// 记录用户点击
#[tauri::command]
pub async fn record_login_click(
    user_id: String,
    environment: String,
) -> Result<(), String> {
    // 使用与前端相同的用户获取逻辑
    let users = get_auto_login_users(environment.clone()).await?;
    let user = users.iter().find(|u| u.userid == user_id)
        .ok_or("用户不存在".to_string())?;

    let now = chrono::Local::now().format("%Y-%m-%d %H:%M:%S").to_string();

    let conn = get_db_connection()?;
    let conn = conn.lock().map_err(|e| format!("获取数据库锁失败: {}", e))?;

    // 检查记录是否存在
    let mut stmt = conn.prepare(
        "SELECT id, click_count FROM login_records WHERE user_id = ?1 AND environment = ?2"
    ).map_err(|e| format!("准备查询语句失败: {}", e))?;

    let existing_record: SqliteResult<(i64, i64)> = stmt.query_row(
        [&user_id, &environment],
        |row| Ok((row.get(0)?, row.get(1)?))
    );

    match existing_record {
        Ok((_, click_count)) => {
            // 更新现有记录
            conn.execute(
                "UPDATE login_records SET click_count = ?1, last_used = ?2, username = ?3, org_name = ?4 
                 WHERE user_id = ?5 AND environment = ?6",
                [
                    &(click_count + 1).to_string(),
                    &now,
                    &user.username,
                    &user.orgname,
                    &user_id,
                    &environment
                ],
            ).map_err(|e| format!("更新记录失败: {}", e))?;
        }
        Err(_) => {
            // 插入新记录
            conn.execute(
                "INSERT INTO login_records (user_id, username, environment, org_name, click_count, last_used, created_at) 
                 VALUES (?1, ?2, ?3, ?4, 1, ?5, ?6)",
                [
                    &user_id,
                    &user.username,
                    &environment,
                    &user.orgname,
                    &now,
                    &now
                ],
            ).map_err(|e| format!("插入记录失败: {}", e))?;
        }
    }

    Ok(())
}

// 获取排行榜数据
#[tauri::command]
pub async fn get_login_ranking(
    environment: Option<String>,
    limit: Option<i64>,
) -> Result<Vec<RankingItem>, String> {
    let limit = limit.unwrap_or(20);
    
    let conn = get_db_connection()?;
    let conn = conn.lock().map_err(|e| format!("获取数据库锁失败: {}", e))?;
    
    let (query, params): (String, Vec<String>) = if let Some(env) = environment {
        (
            format!(
                "SELECT user_id, username, environment, org_name, click_count, last_used, 
                 ROW_NUMBER() OVER (ORDER BY click_count DESC, last_used DESC) as rank 
                 FROM login_records WHERE environment = ?1 
                 ORDER BY click_count DESC, last_used DESC LIMIT {}",
                limit
            ),
            vec![env]
        )
    } else {
        (
            format!(
                "SELECT user_id, username, environment, org_name, click_count, last_used, 
                 ROW_NUMBER() OVER (ORDER BY click_count DESC, last_used DESC) as rank 
                 FROM login_records 
                 ORDER BY click_count DESC, last_used DESC LIMIT {}",
                limit
            ),
            vec![]
        )
    };
    
    let mut stmt = conn.prepare(&query)
        .map_err(|e| format!("准备查询语句失败: {}", e))?;
    
    let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter()
        .map(|p| p as &dyn rusqlite::ToSql)
        .collect();
    
    let ranking_iter = stmt.query_map(&param_refs[..], |row| {
        Ok(RankingItem {
            user_id: row.get(0)?,
            username: row.get(1)?,
            environment: row.get(2)?,
            org_name: row.get(3)?,
            click_count: row.get(4)?,
            last_used: row.get(5)?,
            rank: row.get(6)?,
        })
    }).map_err(|e| format!("查询排行榜失败: {}", e))?;
    
    let mut rankings = Vec::new();
    for ranking in ranking_iter {
        rankings.push(ranking.map_err(|e| format!("处理排行榜数据失败: {}", e))?);
    }
    
    Ok(rankings)
}

// 获取用户的使用统计
#[tauri::command]
pub async fn get_user_stats(
    user_id: String,
) -> Result<Vec<LoginRecord>, String> {
    let conn = get_db_connection()?;
    let conn = conn.lock().map_err(|e| format!("获取数据库锁失败: {}", e))?;
    
    let mut stmt = conn.prepare(
        "SELECT id, user_id, username, environment, org_name, click_count, last_used, created_at 
         FROM login_records WHERE user_id = ?1 ORDER BY click_count DESC"
    ).map_err(|e| format!("准备查询语句失败: {}", e))?;
    
    let record_iter = stmt.query_map([&user_id], |row| {
        Ok(LoginRecord {
            id: Some(row.get(0)?),
            user_id: row.get(1)?,
            username: row.get(2)?,
            environment: row.get(3)?,
            org_name: row.get(4)?,
            click_count: row.get(5)?,
            last_used: row.get(6)?,
            created_at: row.get(7)?,
        })
    }).map_err(|e| format!("查询用户统计失败: {}", e))?;
    
    let mut records = Vec::new();
    for record in record_iter {
        records.push(record.map_err(|e| format!("处理用户统计数据失败: {}", e))?);
    }
    
    Ok(records)
}

#[tauri::command]
pub async fn get_auto_login_users(_environment: String) -> Result<Vec<UserInfo>, String> {
    // 获取用户设置
    let settings = get_user_settings();

    // 使用统一的数据库配置
    let db_config = &settings.database;

    // 首先测试数据库连接
    match test_database_connection(db_config.clone()).await {
        Ok(_) => {
            // 数据库连接成功，从数据库读取用户信息
            get_users_from_database(db_config.clone()).await
        }
        Err(_) => {
            // 数据库连接失败，从本地文件读取
            get_users_from_file()
        }
    }
}

#[tauri::command]
pub async fn get_users_from_database(config: DatabaseConfig) -> Result<Vec<UserInfo>, String> {
    let connect_string = format!(
        "{}:{}/{}",
        config.host, config.port, config.service_name
    );

    match tokio::task::spawn_blocking(move || {
        let connector = Connector::new(&config.username, &config.password, &connect_string);
        
        match connector.connect() {
            Ok(conn) => {
                let sql = r#"
                    SELECT u.userid, u.username, u.pinyin, u.firstpy, o.orgname 
                    FROM tb_user u 
                    LEFT JOIN tb_organization o ON o.orgcode = u.orgcode 
                    WHERE u."VALIDATE" = '1'
                "#;
                
                match conn.query(sql, &[]) {
                    Ok(mut rows) => {
                        let mut users = Vec::new();
                        while let Some(Ok(row)) = rows.next() {
                            let userid: String = row.get(0).unwrap_or_default();
                            let username: String = row.get(1).unwrap_or_default();
                            let pinyin: String = row.get(2).unwrap_or_default();
                            let firstpy: String = row.get(3).unwrap_or_default();
                            let orgname: String = row.get(4).unwrap_or_default();
                            
                            users.push(UserInfo {
                                userid,
                                username,
                                pinyin,
                                firstpy,
                                orgname,
                            });
                        }
                        Ok(users)
                    }
                    Err(e) => Err(format!("查询用户数据失败: {}", e))
                }
            }
            Err(e) => Err(format!("数据库连接失败: {}", e))
        }
    }).await {
        Ok(result) => result,
        Err(e) => Err(format!("查询任务执行失败: {}", e))
    }
}

#[tauri::command]
pub fn get_users_from_file() -> Result<Vec<UserInfo>, String> {
    // 首先尝试从嵌入的JSON数据读取
    match serde_json::from_str::<Vec<UserInfo>>(USER_INFO_JSON) {
        Ok(users) => {
            println!("成功从嵌入的JSON数据加载了 {} 个用户", users.len());
            return Ok(users);
        },
        Err(e) => {
            println!("解析嵌入的用户信息失败: {}", e);
        }
    }
    
    // 如果嵌入数据解析失败，尝试从文件系统读取（开发环境备用）
    let path = get_user_info_file_path()?;
    if let Ok(data) = fs::read_to_string(&path) {
        match serde_json::from_str::<Vec<UserInfo>>(&data) {
            Ok(users) => {
                println!("从文件系统加载了 {} 个用户", users.len());
                return Ok(users);
            },
            Err(e) => println!("解析用户信息文件失败: {}", e)
        }
    }
    
    // 最后的备用方案：创建默认用户
    println!("使用默认用户信息");
    let default_users = vec![
        UserInfo {
            userid: "admin".to_string(),
            username: "管理员".to_string(),
            pinyin: "guanliyuan".to_string(),
            firstpy: "gly".to_string(),
            orgname: "系统管理".to_string(),
        }
    ];
    
    // 尝试保存默认用户信息到文件（如果可能的话）
    if let Ok(data) = serde_json::to_string_pretty(&default_users) {
        let _ = fs::write(&path, data);
    }
    
    Ok(default_users)
}

#[tauri::command]
pub fn generate_login_token(key: String) -> String {
    let today = Local::now().format("%Y%m%d").to_string();
    let combined_data = format!("{}{}", key, today);
    DesUtil::str_enc(&combined_data, DesUtil::KEY1, DesUtil::KEY2, DesUtil::KEY3)
}

#[tauri::command]
pub fn build_auto_login_url(environment: String, userid: String) -> Result<String, String> {
    let settings = get_user_settings();
    let environments = &settings.auto_login.environments;

    let base_url = match environment.as_str() {
        "55" => &environments.env_55,
        "54" => &environments.env_54,
        "localhost" => &environments.localhost,
        _ => return Err("未知的环境类型".to_string())
    };

    if base_url.is_empty() {
        return Err("环境地址未配置".to_string());
    }

    // 获取key，如果为空则使用空字符串
    let key = if environments.key.is_empty() {
        String::new()
    } else {
        environments.key.clone()
    };

    let token = generate_login_token(key);
    let url = format!(
        "{}/rdmp/clientAction.do?method=clients&nextPage=/index/mainFrame.jsp&userid={}&token={}",
        base_url,
        userid.to_uppercase(),
        token
    );

    Ok(url)
}
#[tauri::command]
pub fn get_user_settings() -> UserSettings {
    match get_settings_file_path() {
        Ok(path) => {
            if let Ok(data) = fs::read_to_string(&path) {
                serde_json::from_str(&data).unwrap_or_default()
            } else {
                UserSettings::default()
            }
        }
        Err(_) => UserSettings::default()
    }
}

#[tauri::command]
pub fn save_user_settings(settings: UserSettings) -> bool {
    match get_settings_file_path() {
        Ok(path) => {
            if let Ok(data) = serde_json::to_string_pretty(&settings) {
                fs::write(path, data).is_ok()
            } else {
                false
            }
        }
        Err(_) => false
    }
}

#[tauri::command]
pub async fn test_database_connection(config: DatabaseConfig) -> Result<String, String> {
    // 基本参数验证
    if config.host.is_empty() {
        return Err("主机地址不能为空".to_string());
    }
    if config.username.is_empty() {
        return Err("用户名不能为空".to_string());
    }
    if config.password.is_empty() {
        return Err("密码不能为空".to_string());
    }
    if config.service_name.is_empty() {
        return Err("服务名不能为空".to_string());
    }
    if config.port == 0 {
        return Err("端口号不能为0".to_string());
    }

    // 构建Oracle连接字符串
    let connect_string = format!(
        "{}:{}/{}",
        config.host, config.port, config.service_name
    );

    // 尝试连接Oracle数据库
    match tokio::task::spawn_blocking(move || {
        // 创建连接器
        let connector = Connector::new(&config.username, &config.password, &connect_string);

        // 尝试建立连接
        match connector.connect() {
            Ok(conn) => {
                // 测试连接是否有效 - 执行一个简单的查询
                match conn.query("SELECT 1 FROM DUAL", &[]) {
                    Ok(mut rows) => {
                        if let Some(Ok(_row)) = rows.next() {
                            Ok("数据库连接测试成功！".to_string())
                        } else {
                            Err("连接成功但查询测试失败".to_string())
                        }
                    }
                    Err(e) => Err(format!("查询测试失败: {}", e))
                }
            }
            Err(e) => {
                // 根据错误类型返回更具体的错误信息
                let error_msg = format!("{}", e);
                if error_msg.contains("ORA-01017") {
                    Err("用户名或密码错误".to_string())
                } else if error_msg.contains("ORA-12154") {
                    Err("TNS: 无法解析指定的连接标识符".to_string())
                } else if error_msg.contains("ORA-12514") {
                    Err("TNS: 监听程序当前无法识别连接描述符中请求的服务".to_string())
                } else if error_msg.contains("ORA-12541") {
                    Err("TNS: 没有监听程序".to_string())
                } else if error_msg.contains("ORA-12170") {
                    Err("TNS: 连接超时".to_string())
                } else if error_msg.contains("timeout") || error_msg.contains("Timeout") {
                    Err("连接超时，请检查网络连接和数据库服务状态".to_string())
                } else if error_msg.contains("refused") || error_msg.contains("Refused") {
                    Err("连接被拒绝，请检查主机地址和端口号".to_string())
                } else {
                    Err(format!("数据库连接失败: {}", e))
                }
            }
        }
    }).await {
        Ok(result) => result,
        Err(e) => Err(format!("连接任务执行失败: {}", e))
    }
}

// 添加获取应用数据目录的函数
fn get_app_data_dir() -> Result<PathBuf, String> {
    match dirs::data_dir() {
        Some(mut path) => {
            path.push("rdmp-tools");
            // 确保目录存在
            if let Err(e) = fs::create_dir_all(&path) {
                return Err(format!("创建应用数据目录失败: {}", e));
            }
            Ok(path)
        }
        None => Err("无法获取应用数据目录".to_string())
    }
}

// 获取配置文件路径
fn get_settings_file_path() -> Result<PathBuf, String> {
    let mut path = get_app_data_dir()?;
    path.push("user_settings.json");
    Ok(path)
}

// 获取用户信息文件路径
fn get_user_info_file_path() -> Result<PathBuf, String> {
    let mut path = get_app_data_dir()?;
    path.push("user_info.json");
    Ok(path)
}